﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SQLite;

namespace PTCScheduler
{
    class Teacher
    {
        //Teacher first name.
        private String fname;
        public String FirstName
        {
            get { return fname; }
            set { fname = value; }
        }

        //Teacher last name.
        private String lname;
        public String LastName
        {
            get { return lname; }
            set { lname = value; }
        }

        //Teacher email.
        private String email;
        public String Email
        {
            get { return email; }
            set { email = value; }
        }

        //SQLite Connection
        private SQLiteConnection myConnection;

        //SQLite Transaction
        private SQLiteTransaction transaction;

        //Teacher constructor that takes information as agruments.
        public Teacher(String newfname, String newlname, String newemail)
        {
            //Set datafields.
            this.FirstName = newfname;
            this.LastName = newlname;
            this.Email = newemail;
        }

        //Method to return the teacher insert string.
        public String insertNewTeacherString()
        {
            return @"INSERT INTO teachers (fname, lname, email) 
                     VALUES ('" + this.FirstName + "','" + this.LastName + "','" + this.Email + "')";
        }

        //Method to execute the insert of teacher into database.
        public void AddNewTeacher()
        {
            //Create new sqlite connection
            myConnection = new SQLiteConnection(SystemQueries.dbConnString());

            //Open connection
            myConnection.Open();

            //Set the SQLiteTransaction
            transaction = myConnection.BeginTransaction();

            //Create the SQLite command and set its properties
            SQLiteCommand command = new SQLiteCommand();
            command.Transaction = transaction;
            command.CommandText = this.insertNewTeacherString();
            command.ExecuteNonQuery();

            //Commit the transaction
            transaction.Commit();
            transaction.Dispose();

            //Close connection.
            myConnection.Close();

            //Dispose connection.
            myConnection.Dispose();
        }

        public DataView dvTeacher()
        {
            //Connect to database and open connection.
            myConnection = new SQLiteConnection(SystemQueries.dbConnString());
            myConnection.Open();

            //Retrieve the student query string from the SystemQueries class and set the
            //student id to the desired student ID in studentIDtextbox.
            String sql = SystemQueries.teacherInformationQueryString(this.Email);

            //Create dataset to fill with the results of the query. 
            DataSet ds = new DataSet(); //System.Data assembly

            //User SQLiteDataAdapter to retrieve results. 
            SQLiteDataAdapter da = new SQLiteDataAdapter(sql, SystemQueries.dbConnString());

            //Fill the dataset with the results. 
            da.Fill(ds);

            //Close connection.
            myConnection.Close();

            //Return data set.
            return ds.Tables[0].DefaultView;
        }
    }
}
